using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;
using MOD;


namespace DOM
{
    public  class TranscriptEntryDOM
    {
        public bool InsertTranEntry(string studentId,SectionInfo info)
        {
            OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data\selectCourse.mdb");
            try
            {
                con.Open();
                OleDbCommand cmd = con.CreateCommand();
                cmd.CommandText = "INSERT INTO transcriptEntry (studentId,courseId,credit) VALUES (@studentId,@courseId,@credit)";
                
                cmd.Parameters.AddWithValue("@courseId", info.CourseId);
                cmd.Parameters.AddWithValue("@studentId",studentId);
                cmd.Parameters.AddWithValue("@credit",info.Credit);
              
               // cmd.Parameters.AddWithValue("@grade",info.Grade);
                cmd.ExecuteNonQuery();
                con.Close();
                return true;
            }
            catch (Exception)
            {
               // MessageBox.Show(" database error!" + e.Message);
                return false;

            }

        }

        public DataTable SelectAll()
        {
            OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data\selectCourse.mdb");
            con.Open();
            string queryStr = "SELECT * FROM transcriptEntry ";
            OleDbDataAdapter result = new OleDbDataAdapter(queryStr, con);
            DataTable dt = new DataTable();
            result.Fill(dt);
            con.Close();
            return dt;

        }
        public DataTable SelectById(string id)
        {
            OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data\selectCourse.mdb");
            con.Open();
            string queryStr = "SELECT * FROM transcriptEntry WHERE transcriptEntryId = id";
            OleDbDataAdapter result = new OleDbDataAdapter(queryStr, con);
            DataTable dt = new DataTable();
            result.Fill(dt);
            con.Close();
            return dt;
        }
        public bool UpdateGrade(string studentId, string grade)
        {
            OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data\selectCourse.mdb");
            con.Open();
            string queryString = "UPDATE transcriptEntry SET grade =@grade WHERE studentId=@studentId ";
            OleDbCommand cmd = new OleDbCommand();
            cmd.CommandText = queryString;
            cmd.Parameters.AddWithValue("@studentId", studentId);
            cmd.Parameters.AddWithValue("@grade", grade);
            cmd.ExecuteNonQuery();
            return true;
            
        }
        public bool UpdateTranEntry(string transcriptEntryId, string transcriptId, string studentId, string courseId, string grade, string credit)
        {
            OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data\selectCourse.mdb");
            try
            {
                con.Open();
                string queryStr = "UPDATE transcriptEntry SET courseId=@courseId,transcriptEntryId=@transcriptEntryId,transcriptId=@transcriptId, credit =@credit,studentId=@studentId,grade=@grade,";
                OleDbCommand cmd =new OleDbCommand();
                cmd.CommandText = queryStr;
                cmd.Parameters.AddWithValue("@courseId", courseId);
                cmd.Parameters.AddWithValue("@transcriptEntryId", transcriptEntryId);
                cmd.Parameters.AddWithValue("@transcriptId", transcriptId);
                cmd.Parameters.AddWithValue("@credit", credit);
                cmd.Parameters.AddWithValue("@studentId", studentId);
                cmd.Parameters.AddWithValue("@grade", grade);
                cmd.ExecuteNonQuery();
                con.Close();
                return true;

            }
            catch
            {
                return false;
            }

        }
        public bool DeleteSection(Int32 id)
        {
            OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data\selectCourse.mdb");
            //try
            //{
            con.Open();
            string queryStr = "DELETE * FROM  [transcriptEntry] WHERE transcriptEntryId=@id";
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = con;
            cmd.CommandText = queryStr;
            cmd.Parameters.AddWithValue("@id", id);
            cmd.ExecuteNonQuery();
            con.Close();
            return true;
            //}
            //catch (Exception)
            //{
            //    return false;
            //}
        }
    }
}
